package com.btg.set.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.btg.set.BtgSet;
import com.btg.set.BtgSetPlugin;
import com.btg.set.Dialect;
import com.btg.set.callback.ApplyCallBack;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import java.io.InputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * Created by Administrator on 2016/11/28.
 */
public class BtgSetHelper {

    private static BtgSetPlugin btgSetPlugin = BtgSetPlugin.getBtgSetPlugin();

    private static BtgSetHelper btgSetHelper = new BtgSetHelper();

    public static BtgSetHelper getInstance() {
        return btgSetHelper;
    }

    private int indexNo = 0;

    private BtgSetHelper() {
    }

    //取得重置数据库的sql语句
    public JSONObject getResetSql(Dialect dialect) {
        String xml = "mysql.xml";
        switch (dialect) {
            case MYSQL:
                xml = "mysql.xml";
                break;
            case ORACLE:
                xml = "oracle.xml";
                break;
            case SQLSERVER:
                xml = "sqlserver.xml";
                break;
        }
        JSONObject re = new JSONObject();
        try {
            InputStream in = this.getClass().getResourceAsStream("/com/btg/set/sql/" + xml);
            SAXReader reader = new SAXReader();
            Document document;
            Element root;
            document = reader.read(in);
            root = document.getRootElement();
            for (Iterator<?> i = root.elementIterator("sql"); i.hasNext(); ) {
                Element sql = (Element) i.next();
                String table = sql.attributeValue("table");
                String value = sql.getStringValue();
                re.put(table, value);
            }
        } catch (DocumentException e) {
            e.printStackTrace();
        }
        return re;
    }

    //读取配置文件
    public JSONArray readDefault() {
        JSONArray arr = new JSONArray();
        if (btgSetPlugin.getTemplet() == null) {
            throw new RuntimeException("文件模板参数【setTemplet】未设置值!");
        }
        try {
            InputStream in = this.getClass().getResourceAsStream("/" + btgSetPlugin.getTemplet());

            System.out.println("读取sql初始化模板文件开始!");
            SAXReader reader = new SAXReader();
            Document document;
            Element root;
            document = reader.read(in);
            root = document.getRootElement();

            indexNo = 0;//重置排序号


            arr.addAll(getParam(root, true));
            for (Iterator<?> i = root.elementIterator("module"); i.hasNext(); ) {
                Element module = (Element) i.next();
                arr.addAll(getParam(module, false));
            }
            System.out.println("读取sql初始化模板文件结束!");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return arr;
    }

    private JSONArray getParam(Element el, boolean isRoot) {
        JSONArray arr = new JSONArray();
        String msg = "";
        for (Iterator<?> j = el.elementIterator("param"); j.hasNext(); ) {
            Element param = (Element) j.next();
            String code = param.attributeValue("code");
            if (code == null || "".equals(code)) {
                msg = btgSetPlugin.getTemplet() + " 节点未配置属性【code】" + param.toString();
                throw new RuntimeException(msg);
            }
            JSONObject json = new JSONObject();
            String type = param.attributeValue("type");
            if (type == null || "".equals(type))
                type = "String";
            String name = param.attributeValue("name") != null ? param.attributeValue("name") : "";
            String desc = param.attributeValue("desc") != null ? param.attributeValue("desc") : "";
            String value = param.getTextTrim();

            json.put("code", code.trim());
            json.put("name", name.trim());
            json.put("type", type.trim());
            json.put("desc", desc.trim());
            json.put("value", value);
            json.put("group", isRoot ? "默认" : el.attributeValue("name").trim());
            json.put("indexNo", indexNo++);

            arr.add(json);
        }
        return arr;
    }

    //重置数据库环境
    public void resetDataBaseEnv() {
        Connection conn = null;
        Statement statement = null;
        try {
            conn = btgSetPlugin.getConnection();
            JSONObject re = getResetSql(btgSetPlugin.getDialect());
            statement = conn.createStatement();
            for (String s : re.keySet()) {
                String sql[] = re.getString(s).split(";");
                for (String str : sql) {
                    if (str.trim().length() > 0) {
                        statement.execute(str);

                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null)
                    statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 将默认配置写入数据库
     */
    public void writeDefaultSet() {
        Connection conn = null;
        Statement stmt = null;
        PreparedStatement pstms = null;
        try {
            JSONArray arr = readDefault();
            conn = btgSetPlugin.getConnection();
            stmt = conn.createStatement();
            String defaultCode = "default";
            String defaultName = "默认配置";
            //判断是否存在默认配置
            String sql = "select * from btg_set_cfg_module where set_code='" + defaultCode + "'";

            String keyId = null;
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                keyId = rs.getString("key_id");
                break;
            }
            if (keyId != null) {
                sql = "update btg_set_cfg_module set set_name='" + defaultName + "',state=1 where key_id='" + keyId + "'";
                stmt.execute(sql);
            } else {
                keyId = UUID.randomUUID().toString().replaceAll("-", "");
                sql = "insert into btg_set_cfg_module(key_id,set_code,set_name,set_desc,state)";
                sql += "values(?,?,?,?,1)";
                pstms = conn.prepareStatement(sql);
                pstms.setString(1, keyId);
                pstms.setString(2, defaultCode);
                pstms.setString(3, defaultName);
                pstms.setString(4, defaultName);
                pstms.execute();
                pstms.clearParameters();
                pstms.close();

            }
            //删除默认模板的值
            sql = "delete from btg_set_cfg_miv where module_id='" + keyId + "'";
            stmt.execute(sql);


            for (int i = 0; i < arr.size(); i++) {
                JSONObject json = arr.getJSONObject(i);
                sql = "select * from btg_set_cfg_item where item_code=?";
                pstms = conn.prepareStatement(sql);
                pstms.setString(1, json.getString("name"));
                rs = pstms.executeQuery();
                String itemId = null;
                while (rs.next()) {
                    itemId = rs.getString("key_id");
                    break;
                }
                if (itemId == null) {
                    itemId = UUID.randomUUID().toString().replaceAll("-", "");
                    sql = "insert into btg_set_cfg_item(key_id,item_code,item_name,item_desc,data_type,group_name,index_no)";
                    sql += "values(?,?,?,?,?,?,?)";
                    pstms = conn.prepareStatement(sql);
                    pstms.setString(1, itemId);
                    pstms.setString(2, json.getString("code"));
                    pstms.setString(3, json.getString("name"));
                    pstms.setString(4, json.getString("desc"));
                    pstms.setString(5, json.getString("type"));
                    pstms.setString(6, json.getString("group"));
                    pstms.setInt(7, json.getInteger("indexNo"));
                    pstms.execute();
                }

                sql = "insert into btg_set_cfg_miv(key_id,module_id,module_code,item_code,item_value,remark,i_time)";
                sql += "values(?,?,?,?,?,?,?)";

                SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                String mivId = UUID.randomUUID().toString().replaceAll("-", "");
                pstms = conn.prepareStatement(sql);
                pstms.setString(1, mivId);
                pstms.setString(2, keyId);
                pstms.setString(3, defaultCode);
                pstms.setString(4, json.getString("code"));
                pstms.setString(5, json.getString("value"));
                pstms.setString(6, json.getString("desc"));
                pstms.setString(7, sf.format(new Date()));
                pstms.execute();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstms != null)
                    pstms.close();
                if (stmt != null)
                    stmt.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public JSONObject initValue() {
        JSONObject json = new JSONObject();
        String sql = "select * from btg_set_cfg_module where state=1";
        Connection conn = btgSetPlugin.getConnection();
        Statement statement = null;
        ResultSet rs = null;
        try {
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            String moduleId = null;
            while (rs.next()) {
                moduleId = rs.getString("key_id");
                break;
            }
            json = initValue(moduleId);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (statement != null)
                    statement.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return json;
    }

    public JSONObject initValue(String moduleId) {
        JSONObject json = new JSONObject();
        Connection conn = btgSetPlugin.getConnection();
        Statement statement = null;
        ResultSet rs = null;
        String sql = "select t.*,(select i.data_type from btg_set_cfg_item i where t.item_code=i.item_code)data_type from btg_set_cfg_miv t  where t.module_id='" + moduleId + "'";
        try {
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            String itemCode = null, itemValue = null, dataType = null;
            while (rs.next()) {
                itemCode = rs.getString("item_code");
                itemValue = rs.getString("item_value");
                dataType = rs.getString("data_type");
                json.put(itemCode, itemValue);
            }
            BtgSet.init(json);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (statement != null)
                    statement.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return json;
    }
}
